package com.doule.farmerassistant;

import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;

import com.doule.farmerassistant.task.TaskDetail;

import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.os.Environment;

/**
 * SQLite helper class.
 * 
 * @author happy
 * 
 */
public final class TasksSqlHelper {
    /**
     * Constructor.
     */
    private TasksSqlHelper() {

    }

    /**
     * get database home folder.
     * 
     * @return File
     */
    private static File getDBHomeFolder() {
        File sdPath = Environment.getExternalStorageDirectory();
        File dbPath = new File(sdPath, "idouledb");
        if (!dbPath.exists()) {
            dbPath.mkdir();
        }
        return dbPath;
    }

    /**
     * open or create a database.
     * 
     * @param dbFileName
     *            database file name
     * @return SQLiteDatabase
     */
    private static SQLiteDatabase openOrCreate(final String dbFileName) {
        File dbPath = getDBHomeFolder();

        File dbFilePath = new File(dbPath, dbFileName);
        try {
            SQLiteDatabase db = SQLiteDatabase.openDatabase(
                    dbFilePath.getAbsolutePath(), null,
                    SQLiteDatabase.OPEN_READWRITE
                            | SQLiteDatabase.CREATE_IF_NECESSARY);
            return db;

        } catch (SQLiteException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * open database.
     * 
     * @return SQLiteDatabase
     * @param dbFileName
     *            file name
     * @param sql
     *            sql
     */
    private static SQLiteDatabase openOrCreateDB(final String dbFileName,
            final String sql) {
        SQLiteDatabase db = openOrCreate(dbFileName);
        if (db != null) {
            try {
                db.execSQL(sql);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return db;
    }

    /**
     * open a database.
     * 
     * @param databaseFile
     *            database file name
     * @return SQLiteDatabase
     */
    private static SQLiteDatabase openRead(final String databaseFile) {
        File dbPath = getDBHomeFolder();

        File dbFilePath = new File(dbPath, databaseFile);
        if (!dbFilePath.exists()) {
            return null;
        }

        try {
            SQLiteDatabase db = SQLiteDatabase.openDatabase(
                    dbFilePath.getAbsolutePath(), null,
                    SQLiteDatabase.OPEN_READONLY);
            return db;

        } catch (SQLiteException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 
     * @param dbFileName
     *            Database name
     * @return SQLiteDatabase Database
     */
    private static SQLiteDatabase openDB(final String dbFileName) {
        SQLiteDatabase db = openRead(dbFileName);
        return db;
    }

    // /**
    // * table shoppingcart.
    // */
    // private static final String DB_FILE_SHOPPING_CART = "ishoppingcart.db";
    //
    // /**
    // * create table.
    // */
    // private static final String CART_TABLE_CREATE =
    // "CREATE TABLE IF NOT EXISTS shoppingcart "
    // +
    // "(id TEXT PRIMARY KEY NOT NULL, unitprice TEXT ,totalprice TEXT ,number TEXT,description TEXT,unit TEXT,image TEXT ,name TEXT)";
    // /**
    // * update table.
    // */
    // private static final String CART_TABLE_INSERT =
    // "INSERT OR REPLACE INTO shoppingcart (id,unitprice,totalprice,number,description,unit,image,name) VALUES(?,?,?,?,?,?,?,?)";
    //
    // /**
    // *
    // * @param name
    // * goods name
    // * @param unitprice
    // * goods unit price
    // * @param totalprice
    // * buy money
    // * @param numberint
    // * buy number
    // * @param description
    // * goods description
    // * @param numberfloat
    // * buy number
    // * @param image
    // * goods image
    // * @param id
    // * goods id
    // * @param unit
    // * goods unit
    // *
    // */
    // public static void saveShoppingCart(final String name,
    // final String unitprice, final String totalprice,
    // final String number, final String description, final String unit,
    // final String image, final String id) {
    // SQLiteDatabase db = openOrCreateDB(DB_FILE_SHOPPING_CART,
    // CART_TABLE_CREATE);
    // if (db == null) {
    // return;
    // }
    // try {
    // Object[] args = new Object[8];
    // args[0] = id;
    // args[1] = unitprice;
    // args[2] = totalprice;
    // args[3] = number;
    // args[4] = description;
    // args[5] = unit;
    // args[6] = image;
    // args[7] = name;
    // db.execSQL(CART_TABLE_INSERT, args);
    // } finally {
    // db.close();
    // }
    // }
    //
    // /**
    // *
    // * @param name
    // * goods name
    // * @return goods
    // */
    // public static HashMap<String, String> getShoppingCart(final String id) {
    // SQLiteDatabase db = openDB(DB_FILE_SHOPPING_CART);
    // if (db == null) {
    // return null;
    // }
    // String[] array = new String[1];
    // array[0] = id;
    // Cursor cursor = null;
    // HashMap<String, String> cart = new HashMap<String, String>();
    // try {
    // cursor = db
    // .rawQuery("SELECT * FROM shoppingcart WHERE id=?", array);
    // if ((cursor != null) && cursor.moveToFirst()) {
    // cart.put("name",
    // cursor.getString(cursor.getColumnIndex("name")));
    // cart.put("unitprice",
    // cursor.getString(cursor.getColumnIndex("unitprice")));
    // cart.put("totalprice",
    // cursor.getString(cursor.getColumnIndex("totalprice")));
    // cart.put("number",
    // cursor.getString(cursor.getColumnIndex("number")));
    // cart.put("description",
    // cursor.getString(cursor.getColumnIndex("description")));
    // cart.put("unit",
    // cursor.getString(cursor.getColumnIndex("unit")));
    // cart.put("image",
    // cursor.getString(cursor.getColumnIndex("image")));
    // cart.put("id", cursor.getString(cursor.getColumnIndex("id")));
    // return cart;
    // }
    // } catch (SQLException e) {
    // e.printStackTrace();
    // } finally {
    // if (cursor != null) {
    // cursor.close();
    // }
    // db.close();
    // }
    // return null;
    // }
    //
    // /**
    // * load all goods from shopping cart.
    // *
    // * @return List<HashMap<String, String>>
    // */
    // public static List<HashMap<String, String>> loadShoppingCart(
    // ShoppingCartActivity context) {
    // SQLiteDatabase db = openDB(DB_FILE_SHOPPING_CART);
    // if (db == null) {
    // return null;
    // }
    // Cursor cursor = null;
    // List<HashMap<String, String>> carts = new ArrayList<HashMap<String,
    // String>>();
    // try {
    // cursor = db.rawQuery("SELECT * FROM shoppingcart", null);
    // if (cursor != null && cursor.moveToFirst()) {
    // do {
    // HashMap<String, String> cart = new HashMap<String, String>();
    // cart.put("name",
    // cursor.getString(cursor.getColumnIndex("name")));
    // cart.put("unitprice", cursor.getString(cursor
    // .getColumnIndex("unitprice")));
    // cart.put("totalprice", cursor.getString(cursor
    // .getColumnIndex("totalprice")));
    // cart.put("number",
    // cursor.getString(cursor.getColumnIndex("number")));
    // cart.put("description", cursor.getString(cursor
    // .getColumnIndex("description")));
    // cart.put("unit",
    // cursor.getString(cursor.getColumnIndex("unit")));
    // cart.put("image",
    // cursor.getString(cursor.getColumnIndex("image")));
    // cart.put("id",
    // cursor.getString(cursor.getColumnIndex("id")));
    // carts.add(cart);
    // context.loatShoppingCart(cart);
    // } while (cursor.moveToNext());
    //
    // if (carts.size() > 0) {
    // return carts;
    // }
    // } else {
    //
    // context.getShoppingCartAdapter().notifyDataSetChanged();
    // }
    //
    // } finally {
    // if (cursor != null) {
    // cursor.close();
    // }
    // if (db != null) {
    // db.close();
    // }
    // }
    //
    // return null;
    //
    // }
    //
    // static String cartprice;
    //
    // /**
    // * load all goods from shopping cart.
    // *
    // * @return List<HashMap<String, String>>
    // */
    // public static JSONArray loadShoppingCart() {
    // SQLiteDatabase db = openDB(DB_FILE_SHOPPING_CART);
    // if (db == null) {
    // return null;
    // }
    // DecimalFormat myformat = new DecimalFormat("0.0");
    // Cursor cursor = null;
    // JSONArray carts = new JSONArray();
    // double price = 0;
    // try {
    // cursor = db.rawQuery("SELECT * FROM shoppingcart", null);
    // if (cursor != null) {
    // if (cursor.moveToFirst()) {
    // do {
    // JSONObject cart = new JSONObject();
    // cart.put("name",
    // cursor.getString(cursor.getColumnIndex("name")));
    // cart.put("unitprice", cursor.getString(cursor
    // .getColumnIndex("unitprice")));
    // String total = cursor.getString(cursor
    // .getColumnIndex("totalprice"));
    // cart.put("totalprice", total);
    // cart.put("number", cursor.getString(cursor
    // .getColumnIndex("number")));
    // cart.put("description", cursor.getString(cursor
    // .getColumnIndex("description")));
    // cart.put("unit",
    // cursor.getString(cursor.getColumnIndex("unit")));
    // cart.put("image", cursor.getString(cursor
    // .getColumnIndex("image")));
    // cart.put("id",
    // cursor.getString(cursor.getColumnIndex("id")));
    // price = price + Double.valueOf(total);
    // carts.put(cart);
    // } while (cursor.moveToNext());
    // cartprice = myformat.format(price);
    // if (carts.length() > 0) {
    // return carts;
    // }
    // }
    // }
    // } catch (JSONException e) {
    // // TODO Auto-generated catch block
    // e.printStackTrace();
    // } finally {
    // if (cursor != null) {
    // cursor.close();
    // }
    // if (db != null) {
    // db.close();
    // }
    // }
    // return null;
    // }
    //
    // /**
    // * remove goods.
    // *
    // * @param name
    // * goods name
    // */
    // public static String getCartPrice() {
    // SQLiteDatabase db = openDB(DB_FILE_SHOPPING_CART);
    // if (db == null) {
    // return null;
    // }
    // DecimalFormat myformat = new DecimalFormat("0.0");
    // Cursor cursor = null;
    // double price = 0;
    // try {
    // cursor = db.rawQuery("SELECT * FROM shoppingcart", null);
    // if (cursor != null && cursor.moveToFirst()) {
    // do {
    // String totalpriceS = cursor.getString(cursor
    // .getColumnIndex("totalprice"));
    // price = price + Double.valueOf(totalpriceS);
    // } while (cursor.moveToNext());
    // }
    // } finally {
    // if (cursor != null) {
    // cursor.close();
    // }
    // if (db != null) {
    // db.close();
    // }
    // }
    // return myformat.format(price);
    //
    // }
    //
    // /**
    // * remove goods.
    // *
    // * @param name
    // * goods name
    // */
    // public static void removeGoods(final String id) {
    // SQLiteDatabase db = openOrCreateDB(DB_FILE_SHOPPING_CART,
    // CART_TABLE_CREATE);
    // if (db == null) {
    // return;
    // }
    //
    // try {
    // db.execSQL("delete FROM shoppingcart where id=?",
    // new String[] {/**/id /**/});
    // final int UPDATE_SHOPPING_LIST = 1;
    // Message message = new Message();
    // message.what = UPDATE_SHOPPING_LIST;
    // ShoppingCartActivity.getShoppingCartActivity().myHandler
    // .sendMessage(message);
    // } finally {
    // db.close();
    // }
    // }
    //
    // /**
    // * remove goods.
    // *
    // * @param name
    // * goods name
    // */
    // public static void dropGoods() {
    // SQLiteDatabase db = openOrCreateDB(DB_FILE_SHOPPING_CART,
    // CART_TABLE_CREATE);
    // if (db == null) {
    // return;
    // }
    //
    // try {
    // db.execSQL("delete from shoppingcart");
    // // db.execSQL("drop table shoppingcart");
    // final int UPDATE_SHOPPING_LIST = 1;
    // Message message = new Message();
    // message.what = UPDATE_SHOPPING_LIST;
    // ShoppingCartActivity.getShoppingCartActivity().myHandler
    // .sendMessage(message);
    // } finally {
    // db.close();
    // }
    // }

    /**
     * table shoppingcart.
     */
    private static final String DB_FILE_TASK = "itask.db";
    /**
     * create table.
     */
    private static final String TASK_TABLE_CREATE = "CREATE TABLE IF NOT EXISTS tasks "
            + "(id TEXT PRIMARY KEY NOT NULL, type TEXT ,name TEXT,batchtype TEXT,duration TEXT)";
    /**
     * update table.
     */
    private static final String TASK_TABLE_INSERT = "INSERT OR REPLACE INTO tasks (id,type,name,batchtype,duration) VALUES(?,?,?,?,?)";

    /**
     * Remove task.
     * 
     */
    public static void dropTasks() {
        SQLiteDatabase db = openOrCreateDB(DB_FILE_TASK, TASK_TABLE_CREATE);
        if (db == null) {
            return;
        }

        try {
            db.execSQL("delete from tasks");
            // db.execSQL("drop table shoppingcart");
            // final int UPDATE_SHOPPING_LIST = 1;
            // Message message = new Message();
            // message.what = UPDATE_SHOPPING_LIST;
            // ShoppingCartActivity.getShoppingCartActivity().myHandler
            // .sendMessage(message);
        } finally {
            db.close();
        }
    }

    /**
     * Save task.
     * 
     * @param id
     *            Task id.
     * @param type
     *            Task type
     * @param name
     *            Task name
     * @param batchtype
     *            Batch type of task.
     * @param duration
     *            Duration of task.
     */
    public static void saveTasks(final String id, final String type,
            final String name, final String batchtype, final String duration) {
        SQLiteDatabase db = openOrCreateDB(DB_FILE_TASK, TASK_TABLE_CREATE);
        if (db == null) {
            return;
        }

        try {
            Object[] args = new Object[5];
            args[0] = id;
            args[1] = type;
            args[2] = name;
            args[3] = batchtype;
            args[4] = duration;
            db.execSQL(TASK_TABLE_INSERT, args);
        } finally {
            db.close();
        }
    }

    /**
     * Load all goods from shopping cart.
     * 
     * @param id
     *            Task id
     * @return List<HashMap<String, String>>
     */
    public static HashMap<String, String> loadTasks(String id) {
        SQLiteDatabase db = openDB(DB_FILE_TASK);
        if (db == null) {
            return null;
        }
        String[] array = new String[1];
        array[0] = id;
        Cursor cursor = null;
        try {
            cursor = db.rawQuery("SELECT * FROM tasks WHERE id=?", array);
            if (cursor != null && cursor.moveToFirst()) {
                do {
                    HashMap<String, String> cart = new HashMap<String, String>();
                    cart.put("id",
                            cursor.getString(cursor.getColumnIndex("id")));
                    cart.put("type",
                            cursor.getString(cursor.getColumnIndex("type")));
                    cart.put("name",
                            cursor.getString(cursor.getColumnIndex("name")));
                    cart.put("batchtype", cursor.getString(cursor
                            .getColumnIndex("batchtype")));
                    cart.put("duration",
                            cursor.getString(cursor.getColumnIndex("duration")));
                    return cart;
                } while (cursor.moveToNext());
            }
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            if (db != null) {
                db.close();
            }
        }
        return null;
    }

    /**
     * load all goods from shopping cart.
     * 
     * @return List<HashMap<String, String>>
     */
    public static ArrayList<TaskDetail> loadTasks() {
        SQLiteDatabase db = openDB(DB_FILE_TASK);
        ArrayList<TaskDetail> t = new ArrayList<TaskDetail>();
        if (db == null) {
            return t;
        }
        Cursor cursor = null;
        try {
            cursor = db.rawQuery("SELECT * FROM tasks", null);
            if (cursor != null && cursor.moveToFirst()) {

                do {
                    String id = cursor.getString(cursor.getColumnIndex("id"));
                    String type = cursor.getString(cursor
                            .getColumnIndex("type"));
                    String name = cursor.getString(cursor
                            .getColumnIndex("name"));
                    String batchtype = cursor.getString(cursor
                            .getColumnIndex("batchtype"));
                    String duration = cursor.getString(cursor
                            .getColumnIndex("duration"));
                    TaskDetail detail = new TaskDetail(type, id, name,
                            batchtype, duration, TaskDetail.TaskStatus.Stop);
                    t.add(detail);
                } while (cursor.moveToNext());
                return t;
            }
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            if (db != null) {
                db.close();
            }
        }
        return t;
    }
}
